import datetime

import sqlalchemy
from sqlalchemy import Column, String, Integer, DateTime, Boolean, func
from utils.sql.db import session, Base
from utils.jwt.gettoken import get_pc_token
from utils.sql.app.user import User
from utils.sql.app.feedback import Feedback
from utils.sql.public.message import Message
from utils.sql.app.record import Man_day
from utils.sql.app.advance import Advance
import time
import hashlib
import redis


# 定义User对象:
class Pcuser(Base):
    # 表的名字:
    __tablename__ = 'pc_user'

    # 表的结构:
    id = Column(Integer(), primary_key=True, default=None)
    account = Column(String(20), default='')
    password = Column(String(255), default='')
    phone = Column(String(255), default='')
    name = Column(String(255), default='')
    email = Column(String(255), default='')
    role = Column(String(255), default='')
    forbid = Column(Boolean)
    adduser = Column(String(255), default='')
    adduser_id = Column(String(255), default='')
    create_time = Column(String(20), default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))


# 字符串MD5加密
def md5_encrypt(text):
    # 字符串加盐
    text = "manDay" + text + "pc"
    # 创建MD5对象
    md5 = hashlib.md5()
    # 更新要加密的文本
    md5.update(text.encode('utf-8'))
    # 获取加密后的结果并返回
    return md5.hexdigest()


# 用户登录
async def login(data):
    try:
        user = session.query(Pcuser).filter(
            Pcuser.account == data.account,
            Pcuser.password == md5_encrypt(data.password)
        ).one()  # 添加合适的过滤条件
        if user.forbid:
            return {
                "code": 202,
                "data": '',
                "message": "该用户已被禁止登录，若要继续使用，请与管理员联系！"
            }
        else:
            token = await get_pc_token({
                "id": user.id,
                "role": user.role
            })
            # token存入redis
            r = redis.StrictRedis(host='localhost', port=6379, db=0)
            r.set(user.id, token, ex=1800)
            # print(r.get(user.id).decode('ascii'))
            # print(token)
            # 匹配成功后签发token
            return {
                "code": 200,
                "data": token,
                "message": "登录成功"
            }

    except sqlalchemy.exc.NoResultFound as e:
        return {
            "code": 201,
            "data": e,
            "message": "账号或密码不正确"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 退出登录
async def outLogin(userId):
    try:
        # 连接到Redis
        r = redis.Redis(host='localhost', port=6379, db=0)
        # return
        # 要删除的key
        key_to_delete = userId
        # 删除key
        r.delete(key_to_delete)
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }

    except Exception as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 新增用户
def newUsers(data):
    try:
        # 创建新Message对象:
        new_User = Pcuser(
            account=data.account,
            password=md5_encrypt('e10adc3949ba59abbe56e057f20f883e'),  # 默认密码123456
            phone=data.phone,
            name=data.name,
            email=data.email,
            role=data.role,
            forbid=data.forbid,
            adduser=data.username,
            adduser_id=data.userid,
            create_time=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        )
        # 添加到session:
        session.add(new_User)
        # 提交即保存到数据库:
        session.commit()
        return {
            "code": 200,
            "data": "",
            "message": "提交成功"
        }
    except sqlalchemy.exc.IntegrityError as e:
        return {
            "code": 201,
            "data": e,
            "message": "提交失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 获取个人信息
def userinfo(userid):
    # 查询要更新的记录
    try:
        user = queryUser(userid)  # 添加合适的过滤条件
        del user.password
        return {
            "code": 200,
            "data": user,
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 修改个人信息
def updateuserinfo(data, userid):
    # 查询要更新的记录
    try:
        user = queryUser(userid)  # 添加合适的过滤条件
        # 将属性值设置为新的值
        user.name = data.name
        user.phone = data.phone
        user.email = data.email

        # 提交即保存到数据库:
        session.commit()
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 修改账户密码
def update_pwd(data, userid):
    # 查询要更新的记录
    try:
        user = queryUser(userid)  # 添加合适的过滤条件
        if md5_encrypt(data.olpassword) == user.password:
            user.password = md5_encrypt(data.newpassword)
            # 提交事务
            session.commit()
            return {
                "code": 200,
                "data": '',
                "message": "操作成功"
            }
        else:
            return {
                "code": 201,
                "data": '',
                "message": "原密码不正确"
            }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 获取用户列表
def userList(query: str, pageNum=1, pageSize=10):
    # 查询要更新的记录
    try:
        user = session.query(Pcuser).order_by(Pcuser.create_time.desc())  # 添加合适的过滤条件
        for item in user:
            item.create_time = str(item.create_time)
        start = (int(pageNum) - 1) * int(pageSize)
        # end = int(pageNum) * int(pageSize)
        if query:
            user = user.filter(Pcuser.name.like(f'%{query}%'))
        users = user.limit(pageSize).offset(start).all()
        return {
            "code": 200,
            "data": {
                "list": users,
                "current": pageNum,
                "total": user.count()
            },
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 禁用用户-取消禁用
def forbidden(userid):
    try:
        user = queryUser(userid)  # 添加合适的过滤条件
        if user.forbid:
            user.forbid = False
        else:
            user.forbid = True
            # 提交事务
        session.commit()
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 管理员设置和取消
def isadmin(userid):
    try:
        user = queryUser(userid)  # 添加合适的过滤条件
        if user.role == '0':
            user.role = '1'
        else:
            user.role = '0'
            # 提交事务
        session.commit()
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 删除用户
def deleteUsers(userid):
    try:
        user = session.query(Pcuser).filter(Pcuser.id == userid)
        # 执行删除操作
        user.delete()
        # 提交事务
        session.commit()
        return {
            "code": 200,
            "data": '',
            "message": "操作成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "操作失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 设为管理员-取消管理员
def queryUser(userid):
    query = session.query(Pcuser).filter(Pcuser.id == userid).one()
    return query


# 小程序用户查询
def get_app_users(query: str, pageNum=1, pageSize=10):
    try:
        list = session.query(User).order_by(User.create_time.desc())
        if list:
            start = (int(pageNum) - 1) * int(pageSize)
            # end = int(pageNum) * int(pageSize)
            if query:
                list = list.filter(User.nick_name.like(f'%{query}%'))
            # users = list.slice(start, end).all()
            users = list.limit(pageSize).offset(start).all()
            return {
                "code": 200,
                "data": {
                    "list": users,
                    "current": pageNum,
                    "total": list.count()
                },
                "message": "查询成功"
            }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "查询失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 小程序用户反馈查询
def get_app_feedbacklist(query: str, pageNum=1, pageSize=10):
    try:
        lists = session.query(Feedback).order_by(Feedback.create_time.desc())
        start = (int(pageNum) - 1) * int(pageSize)
        if query:
            lists = lists.filter(Feedback.dispose_state == query)
        feedback = lists.limit(pageSize).offset(start).all()
        return {
            "code": 200,
            "data": {
                "list": feedback,
                "current": pageNum,
                "total": lists.count()
            },
            "message": "查询成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "查询失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 查询反馈详情
def feedback_details(feeid):
    try:
        query = session.query(Feedback).filter(Feedback.id == feeid).one()
        user = session.query(User).filter(User.user_id == query.user_id).one()
        msg = session.query(Message).filter(Message.feedback_id == query.id,
                                            Message.msg_recipient == query.user_id).one()

        query.nickname = user.nick_name
        query.phone = user.phone_num
        query.name = user.user_name
        query.returnContent = msg.msg_cont
        query.returnTime = msg.create_time
        return {
            "code": 200,
            "data": query,
            "message": "查询成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "查询失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 查询app用户数据详情(图表)
def userdetails(userid, year):
    try:
        # 查询用户信息
        user = session.query(User).filter(User.user_id == userid).one()
        # 查询工天月统计表
        manday = getmandaymonthdata(userid, year)
        # 查询预支统计表
        advance = getadvancemonthdata(userid, year)
        # 查询加班时长月统计表
        overtime = getmandayovertimemonthdata(userid, year)
        return {
            "code": 200,
            "data": {
                "userInfo": user,
                "manday": manday,
                "advance": advance,
                "overtime": overtime
            },
            "message": "查询成功"
        }
    except sqlalchemy.exc.DBAPIError as e:
        return {
            "code": 201,
            "data": e,
            "message": "查询失败"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


monthList = {
    1: "一月",
    2: "二月",
    3: "三月",
    4: "四月",
    5: "五月",
    6: "六月",
    7: "七月",
    8: "八月",
    9: "九月",
    10: "十月",
    11: "十一月",
    12: "十二月",
}


# 查询工天月统计表
def getmandaymonthdata(userid, year):
    manday = (
        session.query(
            func.month(Man_day.create_time).label("month"),
            func.coalesce(func.count(Man_day.user_id), 0).label("count")
        )
        .filter(
            func.year(Man_day.create_time) == year,
            func.month(Man_day.create_time) >= 1,
            Man_day.user_id == userid
        )
        .group_by(func.month(Man_day.create_time))
    )
    mandaydict = {month[0]: month[1] for month in manday}
    mandayListData = {
        "month": [],
        "data": []
    }
    for result in range(1, 13):
        data = mandaydict.get(result, 0)
        mandayListData["month"].append(monthList[result])
        mandayListData["data"].append(data)
    return mandayListData


# 查询预支统计表
def getadvancemonthdata(userid, year):
    advance = (
        session.query(
            func.month(Advance.create_time).label("month"),
            func.coalesce(func.count(Advance.user_id), 0).label("count")
        )
        .filter(
            func.year(Advance.create_time) == year,
            func.month(Advance.create_time) >= 1,
            Advance.user_id == userid
        )
        .group_by(func.month(Advance.create_time))
    )
    advancedict = {month[0]: month[1] for month in advance}
    advanceListData = {
        "month": [],
        "data": []
    }
    for result in range(1, 13):
        data = advancedict.get(result, 0)
        advanceListData["month"].append(monthList[result])
        advanceListData["data"].append(data)
    return advanceListData


# 查询加班时长月统计表
def getmandayovertimemonthdata(userid, year):
    overtime = (
        session.query(
            func.month(Man_day.create_time).label("month"),
            func.coalesce(func.sum(Man_day.over_time), 0).label("sum")
        )
        .filter(
            func.year(Man_day.create_time) == year,
            func.month(Man_day.create_time) >= 1,
            Man_day.user_id == userid
        )
        .group_by(func.month(Man_day.create_time))
    )
    overtimedict = {month[0]: month[1] for month in overtime}
    overtimeListData = {
        "month": [],
        "data": []
    }
    for result in range(1, 13):
        data = overtimedict.get(result, 0)
        overtimeListData["month"].append(monthList[result])
        overtimeListData["data"].append(float(data))
    return overtimeListData
